/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBlayer;

import Modellayer.*;
import java.sql.*;
import java.util.ArrayList;
/**
 *
 * @author Søren
 */
public class DBSalesLineItem implements IFDBSalesLineItem {

    private Connection con;
     /** Creates a new instance
     * of DBSalesLineItem */
    public DBSalesLineItem()
    {
      con = DBConnection.getInstance().getDBcon();
    }

	public void insertSalesLine(SalesLineItem saleLine) {

    int rc = -1;
    String query="INSERT INTO persiSalesLineItem(ProductID, salesOrderID, amount)  VALUES('"+
	  		//saleLine.getSalesLineID()  + "','"  +
		    	saleLine.getProduct().getProductID()  + "','"  +
                        saleLine.getSalesOrder().getSalesOrderID() + "','" +
                        saleLine.getAmount() + "')";

      System.out.println("insert : " + query);
      try{ // insert new product
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}//end try
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in saleslineitem db: "+ex);
	   }
	   //return(rc);
	}

    public ArrayList<SalesLineItem> findSalesLine(int salesOrderID)
    {
        ArrayList salesLineObjects = new ArrayList();
        salesLineObjects = miscWhere("salesOrderID = '" + salesOrderID + "'",true);
        return salesLineObjects;
    }

    private ArrayList<SalesLineItem> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<SalesLineItem> list = new ArrayList<SalesLineItem>();

		String query =  buildQuery(wClause);
                System.out.println("DBSalesLineItem" + query);
  		try{ // read from workson
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		 System.out.println("DBSalesLineItem 2" );
			int snr=0;
			while( results.next() ){
		     	 SalesLineItem salesLineObj = new SalesLineItem();
			 salesLineObj = buildSalesLine(results);
                         list.add(salesLineObj);
			}//end while
			stmt.close();
                        if(retrieveAssociation)// for each workson object find the project name
                        {
                        for(SalesLineItem wobj : list){
                        IFDBProduct dbProductObj = new DBProduct();
                        Product productObj = dbProductObj.findProduct(wobj.getProduct().getProductID(), false);
                        System.out.println("Product is seleceted ");
                        wobj.setProduct(productObj);

                            IFDBSalesOrder dbSalesOrder = new DBSalesOrder();
                            int salesOrderID = wobj.getSalesOrder().getSalesOrderID();
                            SalesOrder pobj = dbSalesOrder.findSalesOrder(salesOrderID);
                            wobj.setSalesOrder(pobj);

                            }

                        }

		}//slut try
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
    private SalesLineItem buildSalesLine(ResultSet results)
    {
        SalesLineItem wobj = new SalesLineItem();
        SalesOrder empobj = new SalesOrder();
        Product projobj = new Product();
        wobj.setSalesOrder(empobj); //make reference to employee
        wobj.setProduct(projobj); //make reference to project
         try{
               empobj.setSalesOrderID(results.getInt("salesOrderID"));
               projobj.setProductID(results.getInt("productID"));
               wobj.setSalesLineID(results.getInt("salesLineItemID"));
               wobj.setAmount(results.getInt("amount"));
          }
         catch(Exception e)
         {
             System.out.println("building workson object");
         }

        return wobj;

    }
    //method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT amount, productID, salesOrderID, salesLineItemID FROM persiSalesLineItem";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}
}
